package com.mrzhou.study.poi;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.ParserConfigurationException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

@Slf4j
public class PoiUtil {

    public static final String[] cnTitle = {"id", "编号EC集团客户编码", "真实的EC集团客户编码", "产品订单号", "压制IP","ip数组开始值", "ip数组结束值",
            "压制ID", "启动途径", "压制方向", "压制开始时间", "压制时长", "压制结束时间",
            "关闭类型", "关闭时间", "关闭原因", "集中抗D同步标记", "同步返回信息", "创建时间", "修改时间"};

    public static final String[] enTitle = {"id", "blockTaskNum", "customerNum", "realCustomerNum", "prodordSkuNum", "ip", "ipBigIntStart", "ipBigIntEnd",
            "blockId", "sourceType", "blockRegion", "blockStartTime", "blockDuration", "blockEndTime",
            "stopType", "stopTime", "stopDesc", "syncFlag", "syncInfo", "createDatetime", "updateDatetime"};

    public static void main(String[] args) throws Exception {
        PoiUtil.newInstance().processFirstSheet("C:\\Users\\周强\\Desktop\\biz_block_task.xlsx");
    }

    public static PoiUtil newInstance() {
        return new PoiUtil();
    }

    public void processFirstSheet(String filePath) throws Exception {
        try (OPCPackage opcPackage= OPCPackage.open(filePath, PackageAccess.READ)) {
            XSSFReader reader = new XSSFReader(opcPackage);
            SharedStringsTable stringsTable = reader.getSharedStringsTable();

            XMLReader parser = this.fetchSheetParser(stringsTable);
            try (InputStream sheet = reader.getSheetsData().next()) {
                InputSource inputSource = new InputSource(sheet);
                parser.parse(inputSource);
            }
        }
    }

    public void processAllSheet(String fileName) throws Exception {
        try (OPCPackage opcPackage = OPCPackage.open(fileName, PackageAccess.READ)) {
            XSSFReader reader = new XSSFReader(opcPackage);
            SharedStringsTable stringsTable = reader.getSharedStringsTable();
            XMLReader parser = this.fetchSheetParser(stringsTable);
            Iterator<InputStream> sheets =  reader.getSheetsData();
            while (sheets.hasNext()) {
                System.out.println("Processing new sheet:\n");
                try (InputStream sheet = sheets.next()) {
                    InputSource sheetSource = new InputSource(sheet);
                    parser.parse(sheetSource);
                }
                System.out.println("");
            }
        }
    }


    public XMLReader fetchSheetParser(SharedStringsTable stringsTable) throws SAXException, ParserConfigurationException {
        XMLReader parser = SAXHelper.newXMLReader();
        ContentHandler handler = new SheetHandler(stringsTable);
        parser.setContentHandler(handler);
        return parser;
    }

    public class SheetHandler extends DefaultHandler {

        private SharedStringsTable stringsTable;
        private String lastContents;
        /** 是否为string格式标识 */
        private boolean nextIsString;
        private final LruCache<Integer, String> lruCache = new LruCache(60);

        /**
         * 缓存
         * @param <A>
         * @param <B>
         */
        private class LruCache<A,B> extends LinkedHashMap<A, B> {
            private final int maxEntries;

            public LruCache(final int maxEntries) {
                super(maxEntries + 1, 1.0f, true);
                this.maxEntries = maxEntries;
            }

            @Override
            protected boolean removeEldestEntry(final Map.Entry<A, B> eldest) {
                return super.size() > maxEntries;
            }
        }

        private SheetHandler(SharedStringsTable stringsTable) {
            this.stringsTable = stringsTable;
        }

        @Override
        public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
            // c = cell 单元格
            if (name.equals("c")) {
                System.out.println(attributes.getValue("r") + "-");
                // 如果下一个元素是SST的索引，则将nextIsString标记为true
                //单元格类型
                String cellType = attributes.getValue("t");
                //cellType值 s:字符串 b:布尔 e:错误处理
                if(null != cellType && cellType.equals("s")) {
                    //标识为true 交给后续endElement处理
                    nextIsString = true;
                } else {
                    nextIsString = false;
                }
            }
            // Clear contents cache
            lastContents = "";
        }

        /**
         * 获取单元格对应的索引值或是内容值
         *  单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
         *  单元格类型是布尔值、错误、公式，lasrIndex则是内容值
         * @param ch
         * @param start
         * @param length
         */
        @Override
        public void characters(char[] ch, int start, int length) {
            lastContents += new String(ch, start, length);
        }

        @Override
        public void endElement(String uri, String localName, String name) throws SAXException {
            if (nextIsString) {
                int idx = Integer.parseInt(lastContents);
                lastContents = lruCache.get(idx);
                // 如果内容为空或者Cache中存在相同key, 不保存Cache中
                if (null == lastContents && !lruCache.containsKey(idx)) {
                    lastContents = new XSSFRichTextString(stringsTable.getEntryAt(idx)).toString();
                    lruCache.put(idx, lastContents);
                }
                nextIsString = false;
            }

            if (name.equals("v")) {
                System.out.println("SheetHandler: lastContents = " + lastContents);
            } else {
                if (name.equals("row")) {
                    System.out.println(lruCache);
                    lruCache.clear();
                }
            }

        }

    }


}
